package org.lem

import jxl.Workbook
import jxl.Cell
import java.text.DateFormat
import java.text.SimpleDateFormat;

class ImportService {

  DateFormat df = new SimpleDateFormat("MM/dd/yyyy");
//  DateFormat df = new SimpleDateFormat("MM dd yyyy HH:mm:ss");

  boolean transactional = true

  void importXlsFile(FileInputStream xlsFile) {
    Workbook workbook = Workbook.getWorkbook(xlsFile);
    loadCities workbook
    loadCountries workbook
    loadLanguages workbook
    loadEthnicities workbook
    loadLocations workbook
    loadClassDays workbook
    loadClassTimes workbook
    loadClassSessions workbook
    loadSkillLevels workbook

    loadStudents workbook
    loadVolunteers workbook
    loadStudentLearningSkills workbook
    loadStudentPreferences workbook
    loadStudentLearningActivities workbook
    
    loadVolunteerDaysTimes workbook
    loadPosition workbook
    loadVolunteerPosition workbook

    workbook.close()
  }

  public void loadCities(Workbook workbook) {
    def sheet = workbook.getSheet("CITIES");

    for (Cell cell: sheet.getColumn(0)) {
      def city = new City();
      city.setName cell.getContents()
      city.save()
    }
  }

  public void loadCountries(Workbook workbook) {
    def sheet = workbook.getSheet("COUNTRIES");

    for (Cell cell: sheet.getColumn(0)) {
      def country = new Country();
      country.setName cell.getContents()
      country.save()
    }
  }

  public void loadEthnicities(Workbook workbook) {
    def sheet = workbook.getSheet("ETHNICITIES");

    for (Cell cell: sheet.getColumn(0)) {
      def ethnicity = new Ethnicity();
      ethnicity.setName cell.getContents()
      ethnicity.save()
    }
  }


  public void loadLanguages(Workbook workbook) {
    def sheet = workbook.getSheet("LANGUAGES");

    for (Cell cell: sheet.getColumn(0)) {
      def language = new Language();
      language.setName cell.getContents()
      language.save()
    }
  }

  public void loadLocations(Workbook workbook) { //bad
    def sheet = workbook.getSheet("LOCATIONS");

    for (Cell cell: sheet.getColumn(0)) {
      def location = new Location()
      location.setAbbreviation cell.getContents()
      location.save()
    }
  }

  public void loadClassDays(Workbook workbook) { //bad
    def sheet = workbook.getSheet("CLASS_DAYS");

    for (Cell cell: sheet.getColumn(0)) {
      def classDay = new ClassDay();
      classDay.setName cell.getContents()
      classDay.save()
    }
  }

  public void loadClassSessions(Workbook workbook) {  //bad
    def sheet = workbook.getSheet("CLASS_SESSIONS");

    for (Cell cell: sheet.getColumn(0)) {
      def classSession = new ClassSession();
      classSession.setName cell.getContents()
      classSession.save()
    }
  }

  public void loadClassTimes(Workbook workbook) {
    def sheet = workbook.getSheet("CLASS_TIME");

    for (Cell cell: sheet.getColumn(0)) {
      def classSession = new ClassTime();
      classSession.setTime cell.getContents()
      classSession.save()
    }
  }

  public void loadSkillLevels(Workbook workbook) {
    def sheet = workbook.getSheet("SKILL_LEVELS");

    for (Cell cell: sheet.getColumn(0)) {
      SkillLevel skillLevel = new SkillLevel()
      skillLevel.setSkillLevel Integer.valueOf(cell.getContents())
      skillLevel.save()
    }
  }


  public void loadStudents(Workbook workbook) {
    def sheet = workbook.getSheet("LEM_STUDENTS");

    for (int i = 1; i < sheet.getRows(); i++) {
      Cell[] cells = sheet.getRow(i)
      try {
        Student student = new Student();
        def studentId = Long.valueOf(cells[0].getContents())
        student.setStudentId studentId
        student.setInputBy cells[1].getContents()
        student.setInputDate df.parse(cells[2].getContents())
        student.setFirstName cells[3].getContents()
        student.setMiddleName cells[4].getContents()
        student.setLastName cells[5].getContents()
        student.setHomeAddress cells[6].getContents()
        student.setCity !cells[7].getContents().isEmpty() ? City.findByName(cells[7].getContents()) : City.findByName("Not Specified")
        student.setState cells[8].getContents()
        student.setZip cells[9].getContents()
        student.setEmailAddress cells[12].getContents()
        student.setGender !cells[13].getContents().isEmpty() ? cells[13].getContents() : "Not Specified"
        student.setDateOfBirth !cells[14].getContents().isEmpty() ? df.parse(cells[14].getContents()) : null
        student.setCountryOfBirth !cells[15].getContents().isEmpty() ? Country.findByName(cells[15].getContents()) : Country.findByName("Not Specified")
        /*def nativeLanguage = null
        if(!cells[16].getContents().isEmpty()){
          nativeLanguage = Language.findByName(cells[16].getContents())
        } else {
          nativeLanguage = Language.findByName("Unknown")
        }
        
        student.setNativeLanguage nativeLanguage*/
        student.setNativeLanguage !cells[16].getContents().isEmpty() ? Language.findByName(cells[16].getContents()) : Language.findByName("Not Specified");
        //TODO: straighten out Other Language importing
        /*def otherLanguages = cells[17]
        if (!otherLanguages.getContents().isEmpty()) {
          if (otherLanguages.getContents().contains(",")) {
            for (String language: otherLanguages.getContents().split(",")) {
              student.addToOtherLanguages Language.findByName(language);
            }
          } else {
            student.addToOtherLanguages Language.findByName(otherLanguages.getContents());
          }
        }*/
        student.setEthnicity !cells[18].getContents().isEmpty() ? Ethnicity.findByName(cells[18].getContents()) : Ethnicity.findByName("Not Specified")
        student.setApplicationDate !cells[19].getContents().isEmpty() ? df.parse(cells[19].getContents()) : df.parse(cells[2].getContents())
        student.setHomePhone cells[26].getContents()
        student.setCellPhone cells[27].getContents()
        boolean churchMembership = false
        if (cells[30].getContents().equals("Yes")) churchMembership = true;
        else if (cells[30].getContents().isEmpty() && !cells[31].getContents().isEmpty()) churchMembership = true
        student.setChurchMembership churchMembership
        student.setNameOfChurch cells[31].getContents()
        student.save()
      } catch (Exception e) {
        System.out.println(Long.valueOf(cells[0].getContents()));
        e.printStackTrace()
      }
    }
  }




  public void loadStudentLearningSkills(Workbook workbook) {
    def sheet = workbook.getSheet("LEM_STUDENT_LEARNING_SKILLS");

    for (int i = 1; i < sheet.getRows(); i++) {
      Cell[] cells = sheet.getRow(i)
      try {
        StudentLearningSkill studentLearningSkill = new StudentLearningSkill();
        studentLearningSkill.setInputBy cells[2].getContents()
        studentLearningSkill.setInputDate df.parse(cells[3].getContents())
        //studentLearningSkill.setDateTested df.parse(cells[5].getContents()) // TODO: revert to this line once updated data is available
        studentLearningSkill.setDateTested !cells[5].getContents().isEmpty() ? df.parse(cells[5].getContents()) : df.parse(cells[3].getContents())
        studentLearningSkill.setTestedBy(!cells[6].getContents().isEmpty() ? Volunteer.findByVolunteerId(Long.valueOf(cells[6].getContents())) : null)
        studentLearningSkill.setTestScorePart1 Integer.valueOf(cells[10].getContents())

        def scorePart2
        try {
          scorePart2 = !cells[11].getContents().isEmpty() ? Integer.valueOf(cells[11].getContents()) : 0
        } catch (ArrayIndexOutOfBoundsException ioe) {
          scorePart2 = 0
        }
        studentLearningSkill.setTestScorePart2 scorePart2

        def scorePart3
        try {
          scorePart3 = !cells[12].getContents().isEmpty() ? Integer.valueOf(cells[12].getContents()) : 0
        } catch (ArrayIndexOutOfBoundsException ioe) {
          scorePart3 = 0
        }
        studentLearningSkill.setTestScorePart3 scorePart3

        def scorePart4
        try {
          scorePart4 = !cells[13].getContents().isEmpty() ? Integer.valueOf(cells[13].getContents()) : 0
        } catch (ArrayIndexOutOfBoundsException ioe) {
          scorePart4 = 0
        }
        studentLearningSkill.setTestScorePart4 scorePart4

        studentLearningSkill.save()

        Student student = Student.findByStudentId(Long.valueOf(cells[1].getContents()));
        student.addToStudentLearningSkill studentLearningSkill
        student.save()
      } catch (Exception e) {
        e.printStackTrace()
      }
    }
  }

  public void loadStudentPreferences(Workbook workbook) {
    def learningActivitySheet = workbook.getSheet("LEM_STUDENT_LEARNING_ACTIVITY");
    for (int i = 1; i < learningActivitySheet.getRows(); i++) {
      Cell[] cells = learningActivitySheet.getRow(i);

      try {
        def session
        try {
          session = !cells[15].getContents().isEmpty() ? cells[15].getContents() : null
        } catch (ArrayIndexOutOfBoundsException ioe) {
          session = null
        }

        def inputBy = cells[6].getContents()
        def inputDate = cells[7].getContents()
        def skillLevel = Integer.valueOf(cells[12].getContents())
        def location = cells[3].getContents()
        def classDay = cells[11].getContents()
        def classTime = cells[4].getContents()

        ScheduledClass studentPreference = getOrCreateScheduledClass(inputBy, inputDate, skillLevel, location, classDay, classTime, session)

        Student student = Student.findByStudentId(Long.valueOf(cells[8].getContents()));
        student.addToStudentPreferences studentPreference
        student.save()
      } catch (Exception e) {
        e.printStackTrace()
      }
    }


    def learningSkillsSheet = workbook.getSheet("LEM_STUDENT_LEARNING_SKILLS");

    for (int i = 1; i < learningSkillsSheet.getRows(); i++) {
      Cell[] cells = learningSkillsSheet.getRow(i)
      try {
        def session
        try {
          session = !cells[14].getContents().isEmpty() ? cells[14].getContents() : null
        } catch (ArrayIndexOutOfBoundsException ioe) {
          session = null
        }

        def inputBy = cells[2].getContents()
        def inputDate = cells[3].getContents()
        def skillLevel = Integer.valueOf(cells[4].getContents())
        def location = cells[7].getContents()
        def classDay = cells[8].getContents()
        def classTime = cells[9].getContents()

        ScheduledClass studentPreference = getOrCreateScheduledClass(inputBy, inputDate, skillLevel, location, classDay, classTime, session)

        Student student = Student.findByStudentId(Long.valueOf(cells[1].getContents()));
        student.addToStudentPreferences studentPreference
        student.save()
      } catch (Exception e) {
        e.printStackTrace()
      }
    }
  }

  private ScheduledClass getOrCreateScheduledClass(String inputBy, String inputDate, Integer skillLevel,
                                                   String location, String classDay, String classTime, String session) {

    List<ScheduledClass> result = findScheduledClass(skillLevel, location, classDay, classTime, session)

    ScheduledClass studentPreference
    if (null == result || result.size() == 0) {
      studentPreference = new ScheduledClass();
      studentPreference.setInputBy inputBy
      studentPreference.setInputDate df.parse(inputDate)
      studentPreference.setSkillLevel SkillLevel.findBySkillLevel(skillLevel)
      studentPreference.setLocation Location.findByAbbreviation(location)
      studentPreference.setClassDays ClassDay.findByName(classDay)
      studentPreference.setClassTime ClassTime.findByTime(classTime)

      if (null != session) {
        studentPreference.setSession ClassSession.findByName(session)
      }

      studentPreference.save()
    } else {
      studentPreference = result.get(0);
    }
    return studentPreference
  }

  private List<ScheduledClass> findScheduledClass(int skillLevel, String location, String classDay, String classTime, String session) {
    List<ScheduledClass> result
    def crit = ScheduledClass.createCriteria()
    if (null != session) {
      result = crit {
        eq("skillLevel", SkillLevel.findBySkillLevel(skillLevel))
        and {
          eq("location", Location.findByAbbreviation(location))
          eq("classDays", ClassDay.findByName(classDay))
          eq("classTime", ClassTime.findByTime(classTime))
          eq("session", ClassSession.findByName(session))
        }
        maxResults(1)
      }
    } else {
      result = crit {
        eq("skillLevel", SkillLevel.findBySkillLevel(skillLevel))
        and {
          eq("location", Location.findByAbbreviation(location))
          eq("classDays", ClassDay.findByName(classDay))
          eq("classTime", ClassTime.findByTime(classTime))
          isNull("session")
        }
        maxResults(1)
      }
    }
    return result
  }

  public void loadStudentLearningActivities(Workbook workbook) {
    def sheet = workbook.getSheet("LEM_STUDENT_LEARNING_ACTIVITY");

    for (int i = 1; i < sheet.getRows(); i++) {
      Cell[] cells = sheet.getRow(i)

      def skillLevel
      def location
      def classDay
      def classTime
      def session
      def month
      def year

      try {
        try {
          session = !cells[15].getContents().isEmpty() ? cells[15].getContents() : null
        } catch (ArrayIndexOutOfBoundsException ioe) {
          session = null
        }

        skillLevel = Integer.valueOf(cells[12].getContents())
        location = cells[3].getContents()
        classDay = cells[11].getContents()
        classTime = cells[4].getContents()

        month = cells[9].getContents()
        year = Integer.valueOf(cells[10].getContents())

        ScheduledClass scheduledClass = findScheduledClass(skillLevel, location, classDay, classTime, session).get(0)

        List<ClassRoster> result
        def crit = ClassRoster.createCriteria()
        result = crit {
          eq("month", month)
          and {
            eq("scheduledClass", scheduledClass)
            eq("year", year)
          }
          maxResults(1)
        }

        def classRoster
        if (result.size() == 0) {
          classRoster = new ClassRoster()
          classRoster.setMonth month
          classRoster.setYear year
          classRoster.setScheduledClass scheduledClass
          classRoster.computeScheduledClassDays()
          classRoster.save()
        } else {
          classRoster = result.get(0)
        }

        def student = Student.findByStudentId(Long.valueOf(cells[8].getContents()))
        if (null == student) {
          continue;
        }

        double studentClassroomHoursForMonth = Double.valueOf(cells[5].getContents())

        //round up fractional student time
        if (studentClassroomHoursForMonth % 1.5 != 0) {
          def studentHours = 0
          while (studentHours < studentClassroomHoursForMonth) {
            studentHours += classRoster.hoursPerClass
          }
          studentClassroomHoursForMonth = studentHours
        }

        int classesForMonthCount = classRoster.getClassDates().size()
        if (classesForMonthCount * classRoster.hoursPerClass < studentClassroomHoursForMonth) {
          studentClassroomHoursForMonth = classesForMonthCount * classRoster.hoursPerClass
        }

        def totalClasses = studentClassroomHoursForMonth / classRoster.getHoursPerClass()
        for (int j = 0; j < totalClasses; j++) {
          StudentLearningActivity studentLearningActivity = new StudentLearningActivity()
          studentLearningActivity.setInputBy cells[6].getContents()
          studentLearningActivity.setInputDate df.parse(cells[7].getContents())
          studentLearningActivity.setClassDate classRoster.getClassDates().get(j)
          studentLearningActivity.student = student
          studentLearningActivity.classRoster = classRoster
          studentLearningActivity.save()
        }
        
      } catch (Exception e) {
        System.out.println("Skill Level: " + skillLevel + " Location: " + location + " Class Day: "
                + classDay + " Class Time: " + classTime + " Session: " + session);
        e.printStackTrace()
      }
    }
  }

  public void loadVolunteers(Workbook workbook) {
    def sheet = workbook.getSheet("LEM_VOLUNTEERS");

    Volunteer volunteer

    for (int i = 1; i < sheet.getRows(); i++) {
      Cell[] cells = sheet.getRow(i)
      try {
        volunteer = new Volunteer();
        volunteer.setVolunteerId Long.valueOf(cells[0].getContents())
        volunteer.setLastName cells[1].getContents()
        volunteer.setFirstName cells[2].getContents()

        volunteer.setHomeAddress cells[3].getContents()
        volunteer.setCity City.findByName(cells[4].getContents())
        volunteer.setState cells[5].getContents()
        volunteer.setZip cells[6].getContents()
        volunteer.setHomePhone cells[7].getContents()
        volunteer.setCellPhone cells[8].getContents()
        volunteer.setWorkPhone cells[9].getContents()

        volunteer.setEmailAddress cells[10].getContents()

        volunteer.setInputBy cells[23].getContents()
        volunteer.setInputDate df.parse(cells[24].getContents())

        volunteer.setGender cells[14].getContents()
        volunteer.setDateOfBirth !cells[16].getContents().isEmpty() ? df.parse(cells[16].getContents()) : null

        if (!cells[17].getContents().isEmpty()) {
          Country theCountry = Country.findByName(cells[17].getContents())
          if (theCountry) {
            volunteer.setCountryOfBirth theCountry;
          }
        }

        if (!cells[18].getContents().isEmpty()) {
          Language theLanguage = Language.findByName(cells[18].getContents())
          if (theLanguage) {
            volunteer.setNativeLanguage theLanguage
          }
        }

        volunteer.setEthnicity Ethnicity.findByName(cells[20].getContents())
        volunteer.setApplicationDate !cells[25].getContents().isEmpty() ? df.parse(cells[25].getContents()) : null
/*
   for (String language: cells[17].getContents().split(",")) {
     volunteer.addToOtherLanguages Language.findByName(language);
   }
  */
        volunteer.save()
      } catch (Exception e) {
      }
    }
  }

  public void loadVolunteerDaysTimes(Workbook workbook) {
    String tableName = "LEM_VOLUNTEER_DAYS_TIME"
    def sheet = workbook.getSheet(tableName)

    VolunteerDaysTime volunteerDaysTime

    for (int rowNumber = 1; rowNumber < sheet.getRows(); rowNumber++) {
      Cell[] cells = sheet.getRow(rowNumber)
      try {
        Volunteer volunteer = findVolunteerByFirstAndLastName(tableName, rowNumber, cells[2].getContents())
        if(volunteer == null)
          continue
        volunteerDaysTime = new VolunteerDaysTime(
                volunteerDaysTimeId: Long.parseLong(cells[0].getContents()),
                volunteer: Volunteer.findByVolunteerId(Long.valueOf(cells[1].getContents())),
                inputBy: volunteer,
                inputDate: df.parse(cells[3].getContents()),
                preferredDay: ClassDay.findByName(cells[4].getContents()),
                preferredTime: ClassTime.findByTime(cells[5].getContents()),
                subPreferredDay: cells.length > 6 ? ClassDay.findByName(cells[6].getContents()) : null,
                subPreferredTime: cells.length > 7 ? ClassTime.findByTime(cells[7].getContents()) : null
        )

        volunteerDaysTime.save()
        if (volunteerDaysTime.hasErrors()) {
          println volunteerDaysTime.errors
          throw new Exception(volunteerDaysTime.errors);
        }
      } catch (Exception e) {
        ImportException importException = new ImportException(tableName, rowNumber, e.toString())
        importException.save()
        e.printStackTrace()
      }
    }
  }

  private Volunteer findVolunteerByFirstAndLastName(String tableName, Integer rowNumber, String name) {
    Integer index = name.lastIndexOf(" ")
    String firstName = name.substring(0, index)
    String lastName = name.substring(index + 1)

    def result = Volunteer.findAllByFirstNameAndLastName(firstName, lastName)

    if (result.size() != 1) {
      def output = "found " + result.size() + " volunteer with the same name (" + firstName + ", " + lastName + ")"
      println output
      ImportException importException = new ImportException(tableName, rowNumber, output)
      importException.save()
      return null
    }
    return result.get(0)
  }

  public void loadPosition(Workbook workbook) {
    String tableName = "LEM_VOLUNTEER_POSITIONS"
    def sheet = workbook.getSheet(tableName)
    
    def hashset = new HashSet();
    
    for (int rowNumber = 1; rowNumber < sheet.getRows(); rowNumber++) {
      Cell[] cells = sheet.getRow(rowNumber)
        hashset.add cells[2].getContents()
    }
    String position
    try {
      Post post
      for (name in hashset) {
        post = new Post (
          name:name,
          description:null  
        )
        position = name
        post.save()
      }
    } catch (Exception e) {
      ImportException importException = new ImportException(tableName, position, e.toString())
      importException.save()
      e.printStackTrace()
    }
  }

  public void loadVolunteerPosition(Workbook workbook) {
    String tableName = "LEM_VOLUNTEER_POSITIONS"
    def sheet = workbook.getSheet(tableName)
    
    VolunteerPosition volunteerPosition
    for (int rowNumber = 1; rowNumber < sheet.getRows(); rowNumber++) {
      Cell[] cells = sheet.getRow(rowNumber)
      Volunteer volunteer = findVolunteerByFirstAndLastName(tableName, rowNumber, cells[3].getContents())
      if(volunteer == null)
        continue
      try {
        volunteerPosition = new VolunteerPosition (
          volunteer: Volunteer.findByVolunteerId(cells[1].getContents()),
          post: Post.findByName(cells[2].getContents()),
          inputBy: volunteer,
          inputDate: df.parse(cells[4].getContents())
        )
        volunteerPosition.save()
      } catch (Exception e) {
        ImportException importException = new ImportException(tableName, rowNumber, e.toString())
        importException.save()
        e.printStackTrace()
      }
    }
  }
}
